﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using Common.DBOper;
using System.Diagnostics;

namespace Common
{
    public class MyDb_Access : IDisposable
    {
         /// <summary></summary>
        private enum SqlType : int
        {
            /// <summary>Access</summary>
            Access = 0,

            /// <summary>SQL Server</summary>
            SqlServer
        }

        #region

        /// <summary</summary>
        private SqlType _type;

        /// <summary></summary>
        private OleDbConnection _connection = null;

        /// <summary></summary>
        private OleDbCommand _command = null;

        /// <summary></summary>
        private OleDbDataReader _reader = null;

        /// <summary></summary>
        private List<OleDbParameter> _params = new List<OleDbParameter>();

        public string dbconnectionstring = "";
        #endregion

        #region

        /// <summary></summary>
        public OleDbDataReader DataReader
        {
            get { return _reader; }
        }

        #endregion

        #region

        /// <summary></summary>
        public MyDb_Access(string conn)
        {
            dbconnectionstring = conn;
        }

        #endregion

        #region

        /// <summary></summary>
        public void Dispose()
        {
            Close();
        }

        /// <summary></summary>
        /// <returns></returns>
        public bool Open()
        {
            //SystemINI sysini = SystemINI.ReadINIFile();
            //Cls_SystemSet ss = new Cls_SystemSet();

            //string dbconnectionstring = Mdl_Class.Systemset.DBConnectionString();

            return Open(dbconnectionstring);
        }

        /// <summary>打开连接</summary>
        /// <param _name="connectionString"></param>
        /// <returns></returns>
        public bool Open(string connectionString)
        {


            if (_reader != null)
                _reader.Dispose();

            if (_command != null)
                _command.Dispose();

            if (_connection != null)
                _connection.Dispose();

            _params.Clear();

            _connection = new OleDbConnection();
            _command = new OleDbCommand();


            if (_connection.State == System.Data.ConnectionState.Open)
                return true;

            try
            {
                _connection.ConnectionString = connectionString;

                _connection.Open();
                _command.Connection = _connection;
            }
            catch (Exception ex)
            {

                //Mdl_Class.WriteLog.dbErrLog("数据库连接失败!", ex.ToString());
                return false;
            }

            return true;
        }

        /// <summary>关闭连接</summary>
        public void Close()
        {
            if (_reader != null)
                _reader.Dispose();

            if (_command != null)
                _command.Dispose();

            if (_connection != null)
                _connection.Dispose();
            return;
        }

        /// <summary>SELECT查询</summary>
        /// <param name="sql">SQL</param>
        /// <returns>true或者false</returns>
        public bool ExecuteReader(string sql)
        {
            try
            {
                if (_reader != null)
                    _reader.Dispose();

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                _command.CommandText = sql;

                _reader = _command.ExecuteReader();
            }
            catch (Exception)
            {

                return false;
            }

            return true;
        }

        /// <summary>SELECT查询</summary>
        /// <param name="sql">SQL</param>
        /// <returns>返回一个datatable</returns>
        public DataTable DataTableExecuteReader(string sql)
        {
            DataTable dt = null;
            try
            {
                if (_reader != null)
                    _reader.Dispose();

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                _command.CommandText = sql;
                DataSet ds = new DataSet();
                OleDbDataAdapter sda = new OleDbDataAdapter(_command);
                sda.Fill(ds);
                dt = ds.Tables[0];
                //return dt;
                //_reader = _command.ExecuteReader();
                //_
            }
            catch (Exception)
            {

                return null;
            }

            return dt;
        }




        /// <summary>INSERT丄UPDATE丄DELETE语句</summary>
        /// <param name="sql">sql</param>
        /// <returns>int</returns>
        public int ExecuteNonQuery(string sql)
        {
            int result;

            try
            {
                _command.CommandText = sql;

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                result = _command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                result = -1;
            }

            return result;
        }

        public string ExecuteScalar(string sql)
        {
            string result;

            try
            {
                _command.CommandText = sql;

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                result = Convert.ToString(_command.ExecuteScalar());
            }
            catch (Exception)
            {
                result = "-1";
            }

            return result;
        }




        /// <summary>
        /// 插入振动加速度数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int ExecuteNonQuery_Data(List<Cls_Data> clsdata)
        {
            int result;

            try
            {

                _command.Parameters.Clear();
                string values = "";

                for (int i = 0; i < clsdata.Count; i++)
                {

                    byte[] _1_3octavedB_bytes = FloatToBytes(clsdata[i]._1_3octavedB.ToArray());
                    byte[] AccelerationFFTAmpDB_bytes = FloatToBytes(clsdata[i].AccelerationFFTAmpDB.ToArray());
                    byte[] AccelerationTimewave_bytes = FloatToBytes(clsdata[i].AccelerationTimewave.ToArray());
                    _command.Parameters.Add("@_1_3octavedBS" + i.ToString(), OleDbType.Binary).Value = _1_3octavedB_bytes;
                    _command.Parameters.Add("@AccelerationFFTAmpDBS" + i.ToString(), OleDbType.Binary).Value = AccelerationFFTAmpDB_bytes;
                    _command.Parameters.Add("@AccelerationTimewaveS" + i.ToString(), OleDbType.Binary).Value = AccelerationTimewave_bytes;
                    values = values + string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',@_1_3octavedBS{9},@AccelerationFFTAmpDBS{10},@AccelerationTimewaveS{11})",
                       clsdata[i].TestId,
                      clsdata[i].ChannelId,
                       clsdata[i].TotaldB,
                       clsdata[i].HighdB,
                       clsdata[i].LowdB,
                       clsdata[i].VelocityRMS,
                       clsdata[i].AccelerationFFTAmpDB_length,
                        clsdata[i].AccelerationTimewave_Length,
                       clsdata[i].TestTime,i,i,i);
                        values = values + ",";
                    
                }
                if (values.Length > 0)
                {
                    values = values.Substring(0, values.Length - 1);

                    _command.CommandText = string.Format("insert into {0}(" +
                        "TestId," +
                        "ChannelId," +
                        "TotaldB," +
                        "HighdB," +
                        "LowdB," +
                        "VelocityRMS," +
                        "AccelerationFFTAmpDB_length," +
                        "AccelerationTimewave_length," +
                        "TestTime," +
                        "_1_3octavedB,AccelerationFFTAmpDB,AccelerationTimewave) values " + values, "T_Data");


                    Stopwatch sp = new Stopwatch();
                    sp.Start();
                    result = _command.ExecuteNonQuery();
                    sp.Stop();
                    Console.WriteLine(sp.ElapsedMilliseconds);
                }
                else
                {
                    result = -1;
                }
            }
            catch (Exception ex)
            {
                Mdl_Class.WriteLog.dbErrLog("ExecuteNonQuery_Data()", ex.ToString());
                result = -1;
            }

            return result;
        }



        /// <summary>开始事务</summary>
        /// <remarks>
        /// </remarks>
        public void BeginTransaction()
        {
            _command.Transaction = _connection.BeginTransaction();

        }



        /// <summary>提交事务</summary>
        public void Commit()
        {
            _command.Transaction.Commit();
        }



        /// <summary>回滚事务</summary>
        public void Rollback()
        {
            _command.Transaction.Rollback();
        }

        /// <summary>清除</summary>
        public void ClearParam()
        {
            _params.Clear();
        }

        /// <summary>sql或者access</summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <remarks>

        /// </remarks>
        public void AddParam(string key, object value)
        {
            OleDbParameter param = null;

            switch (_type)
            {
        

                case SqlType.Access:
                    param = new OleDbParameter(key, value);
                    break;
            }

            _params.Add(param);
        }

        #endregion



        public byte[] FloatToBytes(float[] floats)
        {
            byte[] results = new byte[floats.Length * sizeof(float)];
            Buffer.BlockCopy(floats, 0, results, 0, results.Length);
            return results;
        }


        public float[] BytesToFloat(byte[] bytes)
        {
            float[] floats = new float[bytes.Length / sizeof(float)];
            Buffer.BlockCopy(bytes, 0, floats, 0, bytes.Length);
            return floats;
        }
    }
}
